﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Text.RegularExpressions;

namespace AliCMS.CourseModule.OleDb
{
    public class Course : OleDbBase, Dal.ICourse
    {
        public int GetInnerIDFromGuid(Guid guid)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Course.ID, MapType(Alias.Course.ID)).Value = guid;
            Command.CommandText = "SELECT " + MapColumn(Alias.Course.InnerID) + " FROM " + MapTable(Alias.Objects.Course) + " WHERE " + MapColumn(Alias.Course.ID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return 0;
            else
                return (int)result;
        }
        public Guid GetGuidFromInnerID(object id)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Course.InnerID, MapType(Alias.Course.InnerID)).Value = id;
            Command.CommandText = "SELECT " + MapColumn(Alias.Course.ID) + " FROM " + MapTable(Alias.Objects.Course) + " WHERE " + MapColumn(Alias.Course.InnerID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return Guid.Empty;
            else
                return (Guid)result;
        }
        public Dictionary<string, object> Get(object id)
        {
            Dictionary<string, object> result = new Dictionary<string, object>();

            StringBuilder sql = new StringBuilder("SELECT TOP 1");
            sql.Append(" " + MapColumn(Alias.Course.ID));
            sql.Append("," + MapColumn(Alias.Course.InnerID));
			sql.Append("," + MapColumn(Alias.Course.Name, Lang));
			sql.Append("," + MapColumn(Alias.Course.CategoryID));
			sql.Append("," + MapColumn(Alias.Course.Picture));
			sql.Append("," + MapColumn(Alias.Course.Thumbnail));
            sql.Append("," + MapColumn(Alias.Course.MediaType));
            sql.Append("," + MapColumn(Alias.Course.Price));
			sql.Append("," + MapColumn(Alias.Course.TimeUnit));
			sql.Append("," + MapColumn(Alias.Course.TimeAmount));
			sql.Append("," + MapColumn(Alias.Course.IsMemberFree));
			sql.Append("," + MapColumn(Alias.Course.TeacherID));
			sql.Append("," + MapColumn(Alias.Course.Description, Lang));
			sql.Append("," + MapColumn(Alias.Course.Recommend));
			sql.Append("," + MapColumn(Alias.Course.HitCount));
			sql.Append("," + MapColumn(Alias.Course.SaleCount));
			sql.Append("," + MapColumn(Alias.Course.CollectCount));
			sql.Append("," + MapColumn(Alias.Course.CreatorID));
			sql.Append("," + MapColumn(Alias.Course.CreatorName));
			sql.Append("," + MapColumn(Alias.Course.CreateTime));
			sql.Append("," + MapColumn(Alias.Course.UpdatorID));
			sql.Append("," + MapColumn(Alias.Course.UpdatorName));
			sql.Append("," + MapColumn(Alias.Course.UpdateTime));
			sql.Append("," + MapColumn(Alias.Course.IsReleased));
			sql.Append("," + MapColumn(Alias.Course.IsDeleted));
            sql.Append(" FROM " + MapTable(Alias.Objects.Course));

            if (id is Guid)
            {
                sql.Append(" WHERE " + MapColumn(Alias.Course.ID) + "=?");

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.Course.ID, MapType(Alias.Course.ID)).Value = id;
            }
            else
            {
                sql.Append(" WHERE " + MapColumn(Alias.Course.InnerID) + "=?");

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.Course.InnerID, MapType(Alias.Course.InnerID)).Value = id;
            }

            Command.CommandText = sql.ToString();
            DataReader = Command.ExecuteReader();
            if (!DataReader.HasRows)
            {
                DataReader.Close();
                return null;
            }

            DataReader.Read();
            int i = 0;
            result[Alias.Course.ID] = DataReader[i++];
            result[Alias.Course.InnerID] = DataReader[i++];
			result[Alias.Course.Name] = DataReader[i++];
			result[Alias.Course.CategoryID] = DataReader[i++];
			result[Alias.Course.Picture] = DataReader[i++];
			result[Alias.Course.Thumbnail] = DataReader[i++];
            result[Alias.Course.MediaType] = DataReader[i++];
            result[Alias.Course.Price] = DataReader[i++];
			result[Alias.Course.TimeUnit] = DataReader[i++];
			result[Alias.Course.TimeAmount] = DataReader[i++];
			result[Alias.Course.IsMemberFree] = DataReader[i++];
			result[Alias.Course.TeacherID] = DataReader[i++];
			result[Alias.Course.Description] = DataReader[i++];
			result[Alias.Course.Recommend] = DataReader[i++];
			result[Alias.Course.HitCount] = DataReader[i++];
			result[Alias.Course.SaleCount] = DataReader[i++];
			result[Alias.Course.CollectCount] = DataReader[i++];
			result[Alias.Course.CreatorID] = DataReader[i++];
			result[Alias.Course.CreatorName] = DataReader[i++];
			result[Alias.Course.CreateTime] = DataReader[i++];
			result[Alias.Course.UpdatorID] = DataReader[i++];
			result[Alias.Course.UpdatorName] = DataReader[i++];
			result[Alias.Course.UpdateTime] = DataReader[i++];
			result[Alias.Course.IsReleased] = DataReader[i++];
			result[Alias.Course.IsDeleted] = DataReader[i++];
            DataReader.Close();

            return result;
        }
        public void Add(Dictionary<string, object> item)
        {
            #region 设置RowLang
            item.Add(Alias.Course.RowLang, "|" + Lang + "|");
            #endregion

            StringBuilder names = new StringBuilder();
            StringBuilder values = new StringBuilder();

            Command.Parameters.Clear();
            PrepareParameters(names, values, item);

            Command.CommandText = "INSERT INTO " + MapTable(Alias.Objects.Course) + "(" + names + ") VALUES(" + values + ")";
            Command.ExecuteNonQuery();
        }
        public void Modify(int id, Dictionary<string, object> changes)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Course.InnerID, MapType(Alias.Course.InnerID)).Value = id;

            #region 检查并设置RowLang
            Command.CommandText = "SELECT " + MapColumn(Alias.Course.RowLang) + " FROM " + MapTable(Alias.Objects.Course) + " WHERE " + MapColumn(Alias.Course.InnerID) + "=?";
            string rowLang = Command.ExecuteScalar().ToString();
            if (!rowLang.Contains("|" + Lang + "|"))
                changes.Add(Alias.Course.RowLang, rowLang + Lang + "|");
            #endregion

			#region 推荐
			if (changes.ContainsKey(Alias.Course.IsRecommended))
			{
				if (Convert.ToBoolean(changes[Alias.Course.IsRecommended]))
				{
					Command.CommandText = "SELECT MAX(" + MapColumn(Alias.Course.Recommend) + ") + 1 FROM " + MapTable(Alias.Objects.Course);
					object recommend = Command.ExecuteScalar();
					if (recommend == DBNull.Value)
						changes[Alias.Course.Recommend] = 1;
					else
						changes[Alias.Course.Recommend] = recommend;
				}
				else
				{
					changes[Alias.Course.Recommend] = 0;
				}

				changes.Remove(Alias.Course.IsRecommended);
			}
			#endregion

            StringBuilder sql = new StringBuilder("UPDATE " + MapTable(Alias.Objects.Course) + " SET ");
            Command.Parameters.Clear();
            PrepareParameters(sql, changes);
            Command.Parameters.Add("@" + Alias.Course.InnerID, MapType(Alias.Course.InnerID)).Value = id;

            sql.Append(" WHERE " + MapColumn(Alias.Course.InnerID) + "=?");

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public void Delete(int id)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Course.InnerID, MapType(Alias.Course.InnerID)).Value = id;

            StringBuilder sql = new StringBuilder("DELETE FROM " + MapTable(Alias.Objects.Course) + " WHERE " + MapColumn(Alias.Course.InnerID) + "=?");

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public DataTable List(int page, int pageSize, string condition, string sort)
        {
            DataTable result = new DataTable();
            result.Columns.Add(Alias.Course.ID, MapCSType(Alias.Course.ID));
            result.Columns.Add(Alias.Course.InnerID, MapCSType(Alias.Course.InnerID));
			result.Columns.Add(Alias.Course.Name, MapCSType(Alias.Course.Name));
            result.Columns.Add(Alias.Course.CategoryID, MapCSType(Alias.Course.CategoryID));
			result.Columns.Add(Alias.Course.Picture, MapCSType(Alias.Course.Picture));
			result.Columns.Add(Alias.Course.Thumbnail, MapCSType(Alias.Course.Thumbnail));
            result.Columns.Add(Alias.Course.MediaType, MapCSType(Alias.Course.MediaType));
            result.Columns.Add(Alias.Course.Price, MapCSType(Alias.Course.Price));
			result.Columns.Add(Alias.Course.TimeUnit, MapCSType(Alias.Course.TimeUnit));
			result.Columns.Add(Alias.Course.TimeAmount, MapCSType(Alias.Course.TimeAmount));
			result.Columns.Add(Alias.Course.IsMemberFree, MapCSType(Alias.Course.IsMemberFree));
			result.Columns.Add(Alias.Course.TeacherID, MapCSType(Alias.Course.TeacherID));
			result.Columns.Add(Alias.Course.Description, MapCSType(Alias.Course.Description));
			result.Columns.Add(Alias.Course.Recommend, MapCSType(Alias.Course.Recommend));
			result.Columns.Add(Alias.Course.HitCount, MapCSType(Alias.Course.HitCount));
			result.Columns.Add(Alias.Course.SaleCount, MapCSType(Alias.Course.SaleCount));
			result.Columns.Add(Alias.Course.CollectCount, MapCSType(Alias.Course.CollectCount));
			result.Columns.Add(Alias.Course.CreatorID, MapCSType(Alias.Course.CreatorID));
			result.Columns.Add(Alias.Course.CreatorName, MapCSType(Alias.Course.CreatorName));
			result.Columns.Add(Alias.Course.CreateTime, MapCSType(Alias.Course.CreateTime));
			result.Columns.Add(Alias.Course.UpdatorID, MapCSType(Alias.Course.UpdatorID));
			result.Columns.Add(Alias.Course.UpdatorName, MapCSType(Alias.Course.UpdatorName));
			result.Columns.Add(Alias.Course.UpdateTime, MapCSType(Alias.Course.UpdateTime));
			result.Columns.Add(Alias.Course.IsReleased, MapCSType(Alias.Course.IsReleased));
			result.Columns.Add(Alias.Course.IsDeleted, MapCSType(Alias.Course.IsDeleted));

            #region 条件
            Command.Parameters.Clear();
            string parenthesis = String.Empty;
            string join = String.Empty;
            StringBuilder where = new StringBuilder();
            if (!String.IsNullOrEmpty(condition))
            {
                Match match = null;
                where.Append(" WHERE " + Alias.Course.RowLang + " LIKE '%|" + Lang + "|%' AND ");
                where.Append("(" + condition + ")");

				#region IsRecommended
				match = Regex.Match(where.ToString(), @"(?<=(^|\s|\())" + Alias.Course.IsRecommended + @"\s?=\s?(?<v>true|false|0|1)(?=(\s|\)|$))", RegexOptions.Compiled | RegexOptions.IgnoreCase);
				if (match.Success)
				{
					where.Remove(match.Index, match.Length);
					if (match.Groups["v"].Value.ToLower() == "true" || match.Groups["v"].Value == "0")
						where.Insert(match.Index, Alias.Course.Recommend + ">0");
					else
						where.Insert(match.Index, Alias.Course.Recommend + "=0");
				}
				#endregion

                ParseCondition(where, "a");

                #region IncludeSubCategory
                match = Regex.Match(where.ToString(), @"(?<=(^|\s|\())" + Alias.Course.IncludeSubCategory + @"\s?=\s?((?<InnerID>\d+)|(?<Guid>(\{|'|'\{)?[a-fA-F0-9]{8}(-?[a-fA-F0-9]{4}){3}-?[a-fA-F0-9]{12}(\}|'|\}')?))(?=(\s|\)|$))", RegexOptions.Compiled | RegexOptions.IgnoreCase);
                if (match.Success)
                {
                    where.Remove(match.Index, match.Length);

                    object id = null;
                    if (match.Groups["InnerID"].Success)
                    {
                        id = match.Groups["InnerID"].Value;
                    }
                    else
                    {
                        string guid = match.Groups["Guid"].Value;
                        if (guid.StartsWith("{") || guid.StartsWith("'"))
                            guid = guid.Substring(1, guid.Length - 2);

                        bool isMSSql = Connection.ConnectionString.IndexOf("SQLOLEDB", StringComparison.InvariantCultureIgnoreCase) != -1;
                        if (isMSSql)
                            guid = "'" + guid + "'";
                        else
                            guid = "{" + guid + "}";

                        Command.CommandText = "SELECT " + MapColumn(Alias.Category.InnerID) + " FROM " + MapTable(Alias.Objects.Category) + " WHERE " + MapColumn(Alias.Category.ID) + "=" + guid;
                        id = Command.ExecuteScalar();
                    }

                    parenthesis += "(";
                    join += " INNER JOIN " + MapTable(Alias.Objects.Category) + " AS ac ON ac." + MapColumn(Alias.Category.ID) + "=a." + MapColumn(Alias.Course.CategoryID) + ")";
                    where.Insert(match.Index, "(ac." + MapColumn(Alias.Category.InnerID) + "=" + id + " OR ac." + MapColumn(Alias.Category.ParentChain) + " LIKE '%." + id + "' OR ac." + MapColumn(Alias.Category.ParentChain) + " LIKE '%." + id + ".%')");
                }
                #endregion
			}
            else
            {
                where.Append(" WHERE a." + MapColumn(Alias.Course.RowLang) + " LIKE '%|" + Lang + "|%'");
            }

            #endregion

            #region 排序
            if (!String.IsNullOrEmpty(sort))
                sort = " ORDER BY " + ParseSort(sort, "a");
            else
                sort = " ORDER BY a." + MapColumn(Alias.Course.InnerID) + " DESC";
            #endregion

            #region 构造SQL语句
            StringBuilder sql = new StringBuilder("SELECT");
            if (page > 0 && pageSize > 0)
                sql.Append(" TOP " + (page * pageSize));
            sql.Append(" a." + MapColumn(Alias.Course.ID));
            sql.Append(",a." + MapColumn(Alias.Course.InnerID));
			sql.Append(",a." + MapColumn(Alias.Course.Name, Lang));
            sql.Append(",a." + MapColumn(Alias.Course.CategoryID));
			sql.Append(",a." + MapColumn(Alias.Course.Picture));
			sql.Append(",a." + MapColumn(Alias.Course.Thumbnail));
            sql.Append(",a." + MapColumn(Alias.Course.MediaType));
            sql.Append(",a." + MapColumn(Alias.Course.Price));
			sql.Append(",a." + MapColumn(Alias.Course.TimeUnit));
			sql.Append(",a." + MapColumn(Alias.Course.TimeAmount));
			sql.Append(",a." + MapColumn(Alias.Course.IsMemberFree));
			sql.Append(",a." + MapColumn(Alias.Course.TeacherID));
			sql.Append(",a." + MapColumn(Alias.Course.Description, Lang));
			sql.Append(",a." + MapColumn(Alias.Course.Recommend));
			sql.Append(",a." + MapColumn(Alias.Course.HitCount));
			sql.Append(",a." + MapColumn(Alias.Course.SaleCount));
			sql.Append(",a." + MapColumn(Alias.Course.CollectCount));
			sql.Append(",a." + MapColumn(Alias.Course.CreatorID));
			sql.Append(",a." + MapColumn(Alias.Course.CreatorName));
			sql.Append(",a." + MapColumn(Alias.Course.CreateTime));
			sql.Append(",a." + MapColumn(Alias.Course.UpdatorID));
			sql.Append(",a." + MapColumn(Alias.Course.UpdatorName));
			sql.Append(",a." + MapColumn(Alias.Course.UpdateTime));
			sql.Append(",a." + MapColumn(Alias.Course.IsReleased));
			sql.Append(",a." + MapColumn(Alias.Course.IsDeleted));
            sql.Append(" FROM " + parenthesis + MapTable(Alias.Objects.Course) + " AS a");
            sql.Append(join + where + sort);
            Command.CommandText = sql.ToString();
            #endregion

            #region 执行SQL语句并获取记录
            int curIndex = 0, rowIndex = 0;
            int startIndex = (page - 1) * pageSize;
            DataReader = Command.ExecuteReader();
            while (DataReader.Read())
            {
                if (pageSize != -1)
                {
                    if (curIndex < startIndex)
                    {
                        curIndex++;
                        continue;
                    }

                    if (rowIndex == pageSize)
                        break;
                }

                DataRow row = result.NewRow();
                int i = 0;
                row[Alias.Course.ID] = DataReader[i++];
                row[Alias.Course.InnerID] = DataReader[i++];
				row[Alias.Course.Name] = DataReader[i++];
				row[Alias.Course.CategoryID] = DataReader[i++];
				row[Alias.Course.Picture] = DataReader[i++];
				row[Alias.Course.Thumbnail] = DataReader[i++];
                row[Alias.Course.MediaType] = DataReader[i++];
                row[Alias.Course.Price] = DataReader[i++];
				row[Alias.Course.TimeUnit] = DataReader[i++];
				row[Alias.Course.TimeAmount] = DataReader[i++];
				row[Alias.Course.IsMemberFree] = DataReader[i++];
				row[Alias.Course.TeacherID] = DataReader[i++];
				row[Alias.Course.Description] = DataReader[i++];
				row[Alias.Course.Recommend] = DataReader[i++];
				row[Alias.Course.HitCount] = DataReader[i++];
				row[Alias.Course.SaleCount] = DataReader[i++];
				row[Alias.Course.CollectCount] = DataReader[i++];
				row[Alias.Course.CreatorID] = DataReader[i++];
				row[Alias.Course.CreatorName] = DataReader[i++];
				row[Alias.Course.CreateTime] = DataReader[i++];
				row[Alias.Course.UpdatorID] = DataReader[i++];
				row[Alias.Course.UpdatorName] = DataReader[i++];
				row[Alias.Course.UpdateTime] = DataReader[i++];
				row[Alias.Course.IsReleased] = DataReader[i++];
				row[Alias.Course.IsDeleted] = DataReader[i++];
                result.Rows.Add(row);

                rowIndex++;
            }
            DataReader.Close();
            #endregion

            return result;
        }
        public int GetRecordCount(string condition)
        {
			#region 条件
			Command.Parameters.Clear();
			string parenthesis = String.Empty;
			string join = String.Empty;
			StringBuilder where = new StringBuilder();
			if (!String.IsNullOrEmpty(condition))
			{
				Match match = null;
				where.Append(" WHERE " + Alias.Course.RowLang + " LIKE '%|" + Lang + "|%' AND ");
				where.Append("(" + condition + ")");

				ParseCondition(where, "a");

				#region IncludeSubCategory
				match = Regex.Match(where.ToString(), @"(?<=(^|\s|\())" + Alias.Course.IncludeSubCategory + @"\s?=\s?((?<InnerID>\d+)|(?<Guid>(\{|'|'\{)?[a-fA-F0-9]{8}(-?[a-fA-F0-9]{4}){3}-?[a-fA-F0-9]{12}(\}|'|\}')?))(?=(\s|\)|$))", RegexOptions.Compiled | RegexOptions.IgnoreCase);
				if (match.Success)
				{
					where.Remove(match.Index, match.Length);

					object id = null;
					if (match.Groups["InnerID"].Success)
					{
						id = match.Groups["InnerID"].Value;
					}
					else
					{
						string guid = match.Groups["Guid"].Value;
						if (guid.StartsWith("{") || guid.StartsWith("'"))
							guid = guid.Substring(1, guid.Length - 2);

						bool isMSSql = Connection.ConnectionString.IndexOf("SQLOLEDB", StringComparison.InvariantCultureIgnoreCase) != -1;
						if (isMSSql)
							guid = "'" + guid + "'";
						else
							guid = "{" + guid + "}";

						Command.CommandText = "SELECT " + MapColumn(Alias.Category.InnerID) + " FROM " + MapTable(Alias.Objects.Category) + " WHERE " + MapColumn(Alias.Category.ID) + "=" + guid;
						id = Command.ExecuteScalar();
					}

					parenthesis += "(";
					join += " INNER JOIN " + MapTable(Alias.Objects.Category) + " AS ac ON ac." + MapColumn(Alias.Category.ID) + "=a." + MapColumn(Alias.Course.CategoryID) + ")";
					where.Insert(match.Index, "(ac." + MapColumn(Alias.Category.InnerID) + "=" + id + " OR ac." + MapColumn(Alias.Category.ParentChain) + " LIKE '%." + id + "' OR ac." + MapColumn(Alias.Category.ParentChain) + " LIKE '%." + id + ".%')");
				}
				#endregion

				#region IsRecommended
				match = Regex.Match(where.ToString(), @"(?<=(^|\s|\())" + Alias.Course.IsRecommended + @"\s?=\s?(?<v>true|false|0|1)(?=(\s|\)|$))", RegexOptions.Compiled | RegexOptions.IgnoreCase);
				if (match.Success)
				{
					where.Remove(match.Index, match.Length);
					if (match.Groups["v"].Value.ToLower() == "true" || match.Groups["v"].Value == "0")
						where.Insert(match.Index, Alias.Course.Recommend + ">0");
					else
						where.Insert(match.Index, Alias.Course.Recommend + "=0");
				}
				#endregion
			}
			else
			{
				where.Append(" WHERE a." + MapColumn(Alias.Course.RowLang) + " LIKE '%|" + Lang + "|%'");
			}

			#endregion

            Command.CommandText = "SELECT COUNT(*) FROM " + parenthesis + MapTable(Alias.Objects.Course) + " AS a" + join + where;
            return Convert.ToInt32(Command.ExecuteScalar());
        }

        public void IncrementHitCount(int id)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Course.InnerID, MapType(Alias.Course.InnerID)).Value = id;

            Command.CommandText = "UPDATE " + MapTable(Alias.Objects.Course) + " SET " + MapColumn(Alias.Course.HitCount) + "=" + MapColumn(Alias.Course.HitCount) + "+1 WHERE " + MapColumn(Alias.Course.InnerID) + "=?";
            Command.ExecuteNonQuery();
        }
    }
}
